import platform = require('../../../../platform/common/baseCommon');
import dbconnector = require('../../../../platform/server/dataService');
import db = require('../../../../platform/server/data');
import appServer = require('../../../../platform/server/appServer');

let thirdService = new dbconnector.dataTableService();

thirdService.serviceUrl = 'http://192.168.5.39:18080/ATSDT_SRV/HS/JsonRpc/SQLForNodeService';
// thirdService.serviceUrl = 'http://192.168.5.23:8080/ATS5S/HS/JsonRpc/SQLForNodeService';
// thirdService.serviceUrl = 'http://86.36.16.169:8080/ATS55_PT/HS/JsonRpc/SQLForNodeService';
// thirdService.serviceUrl = 'http://86.36.19.15:8080/ATS5S/HS/JsonRpc/SQLForNodeService';

//thirdService.dbconnect = new dbconnector.dbInfoAndConnands();


//数据库连接信息
// let connectInfo = new dbconnector.connectionInfo('ATS55', 'jdbc:oracle:thin:@192.168.5.30:1521/ats60', 'PAO',
//     '3L2o1ngae3591', 'oracle.jdbc.driver.OracleDriver');

// let connectInfo = new dbconnector.connectionInfo('world', 'jdbc:mysql://localhost:3306/world', 'root',
//     'root', 'com.mysql.jdbc.Driver');

let connectInfo = new dbconnector.connectionInfo('fsgs', 'jdbc:oracle:thin:@86.36.16.97/fsgsdw', 'j1_ldm',
    'oracle123', 'oracle.jdbc.driver.OracleDriver');

thirdService.dbConnectInfo = connectInfo;

thirdService.sqlGenerator = new dbconnector.baseDmlStatementGenerator();

//过滤器
let datafilter = new db.baseDataFilter()
    .and(new db.sqlFilter("name", "t.name=?", "国家"));
//测试查询语句
var queryTest = new db.dataCommandInfo();
queryTest.id = 'queryTest';
queryTest.sql = `SELECT * FROM TNMS.T_USER t where {0} `;
queryTest.dataFilter = [datafilter];

// 纳税人信息
let filter_nsrxx = new db.baseDataFilter()
    .and(new db.sqlFilter("r04", "t.R_04=?", "法人财务人员相同")
    , new db.sqlFilter("r11", "t.R_11=?", "注册地址异常")
    , new db.sqlFilter("r15", "t.R_15=?", "相同法人")
    , new db.sqlFilter("r18", "t.R_18=?", "担任非正常户法人财务人员")
    , new db.sqlFilter("r02", "t.R_02=?", "发票大量作废")
    , new db.sqlFilter("r07", "t.R_07=?", "发票大量红字")
    , new db.sqlFilter("r09", "t.R_09=?", "开票金额突增")
    , new db.sqlFilter("r10", "t.R_10=?", "成立时间段规模扩张快")
    , new db.sqlFilter("nsrmc", "b.NSRMC=?", "纳税人名称")
    , new db.sqlFilter("hymc", "b.HYMC=?", "行业名称")
    , new db.sqlFilter("qy", "b.QY=?", "区域")
    , new db.sqlFilter("nsrsbh", "b.NSRSBH=?", "纳税人识别号"));
let cmd_nsrxx = new db.dataCommandInfo();
cmd_nsrxx.id = "cmd_query_xkzzsfpqyfx_nxrxx";
cmd_nsrxx.sql = `select to_char(t.djxh) "登记序号",
    b.nsrmc "纳税人名称",
    b.nsrsbh "纳税人识别号",
    b.hymc "行业",
    b.qy "区域",
    b.scjydz "注册地址",
    b.fddbrxm "法人",
    b.fddbrsfzjhm "法人证件号码"
    from T_FSGS_NSRXX_ZT t, T_FSGS_NSRXX_CL b
    where b.djxh = t.djxh
    and b.yxbz = 'Y'
    and b.nsrzt = '正常' and {0}`;
cmd_nsrxx.dataFilter = [filter_nsrxx];
// 带经纬度纳税人信息
let cmd_nsrxxmap = new db.dataCommandInfo();
cmd_nsrxxmap.id = "cmd_query_xkzzsfpqyfx_nxrxxmap";
cmd_nsrxxmap.sql = `select to_char(t.djxh) "登记序号",
    b.nsrmc "纳税人名称",
    b.nsrsbh "纳税人识别号",
    b.hymc "行业",
    b.qy "区域",
    b.scjydz "注册地址",
    b.fddbrxm "法人",
    b.fddbrsfzjhm "法人证件号码",
    b.longitude "经度",
    b.latitude "纬度"
    from T_FSGS_NSRXX_ZT t, T_FSGS_NSRXX_CL b
    where b.djxh = t.djxh
    and b.yxbz = 'Y'
    and b.nsrzt = '正常' and {0}`;
cmd_nsrxxmap.dataFilter = [filter_nsrxx];

// 纳税人信息扩展
let filter_child = new db.baseDataFilter()
    .add(new db.sqlFilter("djxh", "t.DJXH=?", "登记序号"));
let cmd_nsrxxkz = new db.dataCommandInfo();
cmd_nsrxxkz.id = "cmd_query_xkzzsfpqyfx_nsrxxkz";
cmd_nsrxxkz.sql = `select t.scjydz "生产经营地址",
    to_number(to_char(sysdate, 'yyyy')) -
    to_number(to_char(t.djrq, 'yyyy')) + 1 "设立年限",
    t.fddbrsfzjhm "法人身份证号码",
    kz.fddbryddh "法人联系方式",
    kz.cwfzrsfzjhm "财务人员证件号码",
    kz.cwfzrxm "财务人员姓名",
    kz.cwfzryddh "财务人员联系方式"
    from T_FSGS_NSRXX_CL t, T_FSGS_NSRXX_KZ KZ 
    where t.djxh = kz.djxh and {0}`;
cmd_nsrxxkz.dataFilter = [filter_child];

// 开票信息
let cmd_kpxx = new db.dataCommandInfo();
cmd_kpxx.id = "cmd_query_xkzzsfpqyfx_kpxx";
cmd_kpxx.sql = `select to_char(RQ,'yyyy/MM')            "月份",
    SL            "总数量",
    JE            "总金额",
    SE            "总税额",
    ZFFPSL        "作废数量",
    ZFFPJE        "作废金额",
    ZFFPSE        "作废税额",
    HZFPSL        "红字数量",
    HZFPJE        "红字金额",
    HZFPSE        "红字税额",
    ZCFPSL        "正常开票数量",
    ZCFPJE        "正常开票金额",
    ZCFPSE        "正常开票税额",
    ZFBL          "作废比例",
    HZBL          "红字比例",
    SYZCFPJE      "上月正常开票金额",
    ZCFPJE_HBZCL  "本月金额增长率",
    XYZCFPJE      "下月正常开票金额",
    ZCFPJE_HBZCL2 "下月金额增长率"
    from T_FSGS_NSRXX_FP_DJXH t
    where {0}
    order by t.RQ desc`;
cmd_kpxx.dataFilter = [filter_child];
// 开票信息趋势图
let cmd_kpxxqst = new db.dataCommandInfo();
cmd_kpxxqst.id = "cmd_query_xkzzsfpqyfx_kpxxqst";
cmd_kpxxqst.sql = `select to_char(RQ,'yyyy/MM')     "月份",
    SL     "总数量",
    ZFFPSL "作废数量",
    HZFPSL "红字数量",
    ZCFPJE "正常开票金额"
    from T_FSGS_NSRXX_FP_DJXH t
    where {0}
    order by t.RQ`;
cmd_kpxxqst.dataFilter = [filter_child];
// 作废发票信息
let cmd_zffpxx = new db.dataCommandInfo();
cmd_zffpxx.id = "cmd_query_xkzzsfpqyfx_zffpxx";
cmd_zffpxx.sql = `select to_char(RQ,'yyyy/MM')     "月份",
    SL     "总数量",
    ZFFPSL "作废数量",
    ZFFPJE "作废金额",
    ZFFPSE "作废税额",
    ZFBL   "作废比例"
    from T_FSGS_NSRXX_FP_DJXH t
    where {0}
    order by t.RQ desc`;
cmd_zffpxx.dataFilter = [filter_child];
// 年度发票信息占比情况
let cmd_ndfpzb = new db.dataCommandInfo();
cmd_ndfpzb.id = "cmd_query_xkzzsfpqyfx_ndfpzb";
cmd_ndfpzb.sql = `select to_char(t.nf, 'yyyy') "年份",
    t.hzfpsl "红字数量",
    t.zffpsl "作废数量",
    (t.sl - t.zffpsl - t.hzfpsl) "正常开票数量"
    from t_fsgs_nsrxx_fp_hz_zf_nd t
    where {0}
    order by t.nf desc`;
cmd_ndfpzb.dataFilter = [filter_child];
// 红字发票信息
let cmd_hzfpxx = new db.dataCommandInfo();
cmd_hzfpxx.id = "cmd_query_xkzzsfpqyfx_hzfpxx";
cmd_hzfpxx.sql = `select to_char(RQ,'yyyy/MM')     "月份",
    SL     "总数量",
    HZFPSL "红字数量",
    HZFPJE "红字金额",
    HZFPSE "红字税额",
    HZBL   "红字比例"
    from T_FSGS_NSRXX_FP_DJXH t
    where {0}
    order by t.RQ desc`;
cmd_hzfpxx.dataFilter = [filter_child];
// 利润表
let cmd_lrb = new db.dataCommandInfo();
cmd_lrb.id = "cmd_query_xkzzsfpqyfx_lrb";
cmd_lrb.sql = `select to_char(t.ssqq, 'yyyy') "年份",
    to_char(t.ssqq, 'yyyy/mm') "所属期起月份",
    to_char(t.ssqz, 'yyyy/mm') "所属期止月份",
    t.zyywcb "主营业务成本",
    t.zyywsr "主营业务收入",
    t.jlr "净利润",
    t.lrze "利润总额",
    t.cbfy "成本费用",
    t.tzsy "投资收益",
    t.glfy "管理费用",
    t.yylr "营业利润",
    t.yywzc "营业外支出",
    t.yywsr "营业外收入",
    t.yyfy "营业费用",
    t.cwfy "财务费用",
    t.xsfy "销售费用",
    t.btsr "补贴收入"
    from T_FSGS_CBLRB t
    where t.ssqq < date '2017-10-1'
    and {0}
    order by t.ssqq desc`;
cmd_lrb.dataFilter = [filter_child];
// 资产负债表
let cmd_zcfzb = new db.dataCommandInfo();
cmd_zcfzb.id = "cmd_query_xkzzsfpqyfx_zcfzb";
cmd_zcfzb.sql = `select to_char(t.ssqq, 'yyyy') "年份",
    to_char(t.ssqq, 'yyyy/mm') "所属期起月份",
    to_char(t.ssqz, 'yyyy/mm') "所属期止月份",   
    t.zc "资产合计",
    t.gdzchj "固定资产合计",
    t.hbzj "货币资金",
    t.fldzchj "非流动资产合计",
    t.ldzchj "流动资产合计",
    t.yszk "应收账款",
    t.ch "存货",
    t.qtysk "其他应收账款",
    t.gdzczmjz "固定资产账面价值",
    b.fz "负债合计",
    b.wfplr "未分配利润",
    b.fldfzhj "非流动负债合计",
    b.ldfzhj "流动负债合计",
    b.shzb "实收资本",
    b.yfzk "应付账款",
    b.qtyfk "其他应付款"
    from t_fsgs_zcxxb t, t_fsgs_fzxxb b
    where b.djxh = t.djxh
    and b.ssqq = t.ssqq
    and b.ssqz = t.ssqz
    and t.ssqq < date '2017-10-1'
    and {0}
    order by t.ssqq desc`;
cmd_zcfzb.dataFilter = [filter_child];
// 法人关联信息
let cmd_frglxx = new db.dataCommandInfo();
cmd_frglxx.id = "cmd_query_xkzzsfpqyfx_frglxx";
cmd_frglxx.sql = `select t.nsrmc "纳税人名称",
    t.scjydz "生产经营地址",
    t.qy "区域",
    to_number(to_char(sysdate, 'yyyy')) -
    to_number(to_char(t.djrq, 'yyyy')) + 1 "设立年限",
    t.fddbrsfzjhm "法人证件号码",
    t.fddbrxm "法人姓名",
    kz.fddbryddh "法人联系方式",
    kz.cwfzrsfzjhm "财务人员证件号码",
    kz.cwfzrxm "财务人员姓名",
    kz.cwfzryddh "财务人员联系方式"
    from T_FSGS_NSRXX_CL t, T_FSGS_NSRXX_KZ KZ
    where t.djxh = kz.djxh
    and {0}`;
let filter_frglxx = new db.baseDataFilter()
    .or(new db.sqlFilter("fddbrsfzjhm", "t.fddbrsfzjhm = ?", "法人证件号码")
    , new db.sqlFilter("cwfzrsfzjhm", "kz.cwfzrsfzjhm = ?", "法人证件号码")
    , new db.sqlFilter("bsrsfzjhm", "kz.bsrsfzjhm = ?", "法人证件号码")
    , new db.sqlFilter("fddbrxm", "t.fddbrxm = ?", "法人姓名")
    , new db.sqlFilter("cwfzrxm", "kz.cwfzrxm = ?", "法人姓名")
    , new db.sqlFilter("bsrxm", "kz.bsrxm = ?", "法人姓名"))
cmd_frglxx.dataFilter = [filter_frglxx];

/**
 * 测试1
 */
let cmd_test1 = new db.dataCommandInfo();
cmd_test1.id = 'cmd_test1';
cmd_test1.name = '测试1';
cmd_test1.sql = `select 
    t.FDDBRXM as SHOWNAME,
    t.FDDBRSFZJHM
  from t_fsgs_nsrxx_cl t
    where rownum = 1 and {0}`;
/** 数据过滤器 */
let filter_test1 = new db.baseDataFilter()
    .and(
    new db.sqlFilter("FDDBRSFZJHM", "t.FDDBRSFZJHM = ?", "法人身份证")
    );
cmd_test1.dataFilter = [filter_test1];

/**
* 测试2
*/
let cmd_test2 = new db.dataCommandInfo();
cmd_test2.id = 'cmd_test2';
cmd_test2.name = '测试2';
cmd_test2.sql = `SELECT 
 t.nsrmc AS SHOWNAME,
 t.qy,
 t.scjydz,
 t.hymc,
 TO_NUMBER(TO_CHAR(sysdate, 'yyyy')) - TO_NUMBER(TO_CHAR(t.djrq, 'yyyy')) + 1 slnx,
 t.fddbrsfzjhm,
 kz.fddbryddh,
 kz.cwfzrsfzjhm,
 kz.cwfzrxm,
 kz.cwfzryddh
 FROM
 T_FSGS_NSRXX_CL t,
 t_fsgs_nsrxx_kz KZ
 WHERE
 t.djxh = kz.djxh AND ({0})`;
/** 数据过滤器 */
let filter_test2 = new db.baseDataFilter()
    .or(
    new db.sqlFilter("FDDBRSFZJHM", "t.FDDBRSFZJHM = ?", "法人身份证")
    , new db.sqlFilter("CWFZRSFZJHM", "KZ.CWFZRSFZJHM = ?", "法人身份证")
    );
cmd_test2.dataFilter = [filter_test2];

thirdService.commandList = [queryTest, cmd_nsrxx
    , cmd_kpxx, cmd_nsrxxkz
    , cmd_kpxxqst, cmd_zffpxx
    , cmd_ndfpzb, cmd_hzfpxx
    , cmd_lrb, cmd_zcfzb
    , cmd_zcfzb, cmd_frglxx, cmd_nsrxxmap
    , cmd_test1
    , cmd_test2];

//加到服务列表
platform.fetchAddonProtoType(thirdService);
export let dataServiceInfo = new appServer.serviceInfo();
dataServiceInfo.serviceName = "testexample";
dataServiceInfo.serviceObject = thirdService;







